package com.xxx.excel;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Excel导出工具类
 */
public class ExcelExport {

	private static final Logger logger = LoggerFactory.getLogger(ExcelExport.class);
	
	private ExcelExport() {
		super();
	}
	
	/**
	 * 单例
	 */
	public static ExcelExport getInstance() {
		return new ExcelExport();
	}

	@SuppressWarnings("deprecation")
	public void export(HttpServletResponse response, List<List<Object>> list, String filename, String[] headers) {
		XSSFWorkbook wb = new XSSFWorkbook(); // 创建workbook
		XSSFSheet sheet = wb.createSheet(filename); // 创建sheet
		sheet.setDefaultColumnWidth(20); // 设置sheet默认宽度
		XSSFRow row = sheet.createRow((int) 0); // 创建第0个row，用于存放headers

		XSSFFont font = wb.createFont(); // 创建font
		font.setFontHeightInPoints((short) 11);
		font.setFontName("宋体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		
		XSSFCellStyle style = wb.createCellStyle(); // 创建cell的style
		style.setFont(font);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setFillForegroundColor(HSSFColor.GREY_80_PERCENT.index);

		// 设置header
		XSSFCell cell = null;
		for (int i=0; i<headers.length; i++) {
			cell = row.createCell((short) i);
			cell.setCellValue(headers[i]);
			cell.setCellStyle(style);
		}

		for (int i=0; i<list.size(); i++) {
			row = sheet.createRow((int) i + 1);// 创建row
			
			List<Object> clist = list.get(i);
			for (int n = 0; n < clist.size(); n++) {
				Object value = clist.get(n);
				if (null == value) {
					row.createCell((short) n).setCellValue(""); // 创建cell
				} else if (value instanceof Date) {
					SimpleDateFormat fmt = new SimpleDateFormat("yyyy/MM/dd");
					row.createCell((short) n).setCellValue(fmt.format(value)); // 创建cell
				} else {
					row.createCell((short) n).setCellValue(clist.get(n).toString()); // 创建cell
				}
			}
		}

		try {
			filename = new String(filename.getBytes("UTF-8"), "ISO8859-1");
			response.setContentType("application/force-download");
			response.setHeader("Content-Disposition", "attachment;filename=\"" + filename + ".xlsx" + "\" ");
			wb.write(response.getOutputStream());
			response.getOutputStream().flush();
			response.getOutputStream().close();
			wb.close();
		} catch (Exception e) {
			logger.error("导出excel失败", e);
		}
	}

}
